# -*- coding: utf-8 -*-
# @File    : runMainFun.py
# @Date    : 2021-08-27
# @Author  : Richard

import os
import time
from app.get_base_info import Read_ini
from app.get_server_info import ExcelData
from app.get_sql_stam import ResoluSql
from app.db import DB
from app.get_comp_info import GetCompName
from app.set_log import write_log
from app.run_sql_stam import run_sql_tra
from explog.out_log import log_out
from app.get_guid import get_guid


# 主函数
def main():
    # 数据库及文件目录信息
    read_ini = Read_ini()
    dir_data = read_ini.values_by_key(sec="dir")
    db_data = read_ini.values_by_key(sec="database")
    # 服务器IP地址文件
    xls_dir = os.path.join(dir_data[0], "cnf\serverInfo.xlsx")
    # SQL文件所在目录
    sql_dir = os.path.join(dir_data[0], "sql")
    # 单位字典文件所在目录
    com_dir = os.path.join(dir_data[0], "cnf\comp_info.ini")
    # 获取服务器IP
    get_excel_data = ExcelData(file=xls_dir, sh=db_data[3])
    server_data = get_excel_data.readExcel()
    # print(dir_data)
    # print(db_data)
    # print(xls_dir)
    # print(sql_dir)
    # print(com_dir)
    # time.sleep(10000)

    # 获取SQL文件
    resolusql = ResoluSql(file=sql_dir)
    sql_file = resolusql.get_sql_file()
    # 获取单位信息
    getCompName = GetCompName(file=com_dir)

    # 开始处理SQL语句
    for itmes in server_data:
        # 数据库IP
        ip = itmes[db_data[2]]
        # 获取租户ID
        tena_id = itmes[db_data[9]]
        # h获取url
        url_id = itmes[db_data[10]]
        # 获取ID
        id_info = itmes[db_data[7]]
        # 获取guid
        guid_info = itmes[db_data[8]]
        # 获取单位名称
        comp_name = getCompName.get_comp(itmes[db_data[2]])
        log_out(msg="正在[{0}]单位执行".format(comp_name))
        # 遍历SQL文件
        for sf in sql_file:
            # 非行方式获取SQL文件
            if int(dir_data[-1]) == 0:
                # 获取SQL语句
                sqls = resolusql.read(sf)
                # 这里要替换一些东西，当你需要的时候（没有关键词就不用替换）
                new_sql = str(sqls).replace("#id9#", str(id_info)).replace("#ouguid#", str(guid_info))
                # 吸入日志
                write_log(msg_info="即将在【{0}】执行:\n{1}".format(comp_name, new_sql))
                # 打印
                log_out(msg=new_sql)
                db = DB(sql=new_sql, ip=ip, user=db_data[0], port=db_data[1])
                # 执行SQL语句
                flag = db.run_sql()
                # 执行SQL后状态值
                # flag = 1
                if int(flag) == 1:
                    write_log(msg_info="【{0}】执行成功!\n".format(comp_name))
                    print("【{0}】执行成功！！\n".format(comp_name))
                else:
                    print("【{0}】执行失败！！".format(comp_name))
                    write_log("【{0}】执行失败! 单位：{1},IP:{2},详细SQL:{3}\n".format(comp_name, comp_name, ip, sqls))
            # 行方式获取文件
            elif int(dir_data[-1]) == 1:
                sqls = resolusql.read_by_row(file=sf)
                for sql in sqls:
                    print("sql:{0}".format(sql))
                    write_log(msg_info="即将在【{0}】执行:\n{1}".format(comp_name, sql))
                    db = DB(sql=sql, ip=ip, user=db_data[0], port=db_data[1])
                    # 执行SQL
                    flag = db.run_sql()
                    # flag = 1
                    # 执行状态
                    if int(flag) == 1:
                        write_log(msg_info="【{0}】执行成功!\n".format(comp_name))
                        print("【{0}】执行成功！！".format(comp_name))
                    else:
                        write_log("【{0}】执行失败! 单位：{1},IP:{2},详细SQL:{3}\n".format(comp_name, comp_name, ip, sql))
                        print("【{0}】执行失败！！".format(comp_name))
            # 创建表
            elif int(dir_data[-1]) == 3:
                sqls = resolusql.open_sql_file(sql_file=sf)
                for sql in sqls[0]:
                    print("sql:{0}".format(sql))
                    write_log(msg_info="即将在【{0}】执行:\n{1}".format(comp_name, sql))
                    db = DB(sql=sql, ip=ip, user=db_data[0], port=db_data[1])
                    flag = db.run_sql()
                    # flag = 1
                    if int(flag) == 1:
                        write_log(msg_info="【{0}】执行成功!\n".format(comp_name))
                        print("【{0}】执行成功！！".format(comp_name))
                    else:
                        write_log("【{0}】执行失败! 单位：{1},IP:{2},详细SQL:{3}\n".format(comp_name, comp_name, ip, sql))
                        print("【{0}】执行失败！！".format(comp_name))
            # 作为一个事务执行
            elif int(dir_data[-1]) == 2:
                sqls = resolusql.read_by_row(file=sf)
                run_sql_tra(ip=ip, user=db_data[0], port=db_data[1], data=sqls, com_file=dir_data[2])
            elif int(dir_data[-1]) == 4:
                # 获取guid
                guid_info = get_guid(ip=ip, user=db_data[0], port=db_data[1], tena_id=tena_id)
                # 检测guid是否为空
                if guid_info[0][0]:
                    # log_out(msg="长度：{0}".format(guid_info[0][0]))
                    # time.sleep(1000)
                    # 获取SQL
                    sqls = resolusql.read(sql_file=sf)
                    sql = str(sqls).replace("#tena_id#", tena_id).replace("#guid#", str(guid_info[0][0]))
                    log_out(msg="sql:{0}".format(sql))
                    # time.sleep(10000)
                    # 实例化数据库
                    db = DB(sql=sql, ip=ip, user=db_data[0], port=db_data[1])
                    # 执行数据库
                    flag = db.run_sql()
                    # flag = 1
                    if int(flag) == 1:
                        write_log(msg_info="【{0}】执行成功!\n".format(comp_name))
                        log_out(msg="【{0}】执行成功！！\n".format(comp_name))
                    else:
                        log_out(msg="【{0}】执行失败！！".format(comp_name))
                        write_log("【{0}】执行失败! 单位：{1},IP:{2},详细SQL:{3}\n".format(comp_name, comp_name, ip, sql))
                else:
                    print("guid长度不符合长度！")


if __name__ == '__main__':
    main()
